package user;		// YOU MAY CHANGE THIS PACKAGE NAME TO FIT INTO YOUR CODE!

import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

/**
 * 
 * @author Jimmy DONG
 * @version alpha 0.0.1
 *
 */
public class DBConnector {
	private static DBConnector dbConnector;		// Singleton. Only one DBConnector instance will exist
	private Connection conn;
	private Statement stmt;
	private PreparedStatement pstmt;
	
	/**
	 * Private constructor can only be used within the class scope
	 * So, no one can create a new instance outside the class
	 * Thus, we can guarantee that only the singleton will be active
	 */
	private DBConnector() {
		init();
	}
	
	/**
	 * Static method returns the singleton
	 * This method should be invoked every time you want to use a DBConnector
	 * @return the singleton DBConnector
	 */
	public static DBConnector getDBConnector() {
		if (dbConnector == null)
			dbConnector = new DBConnector();
		return dbConnector;
	}
	
	/**
	 * Sets up the connection with the database
	 */
	private void init() {
		String driver = "com.mysql.jdbc.Driver";
		
		// TODO You may change this connection to your own MySQL database
		String connection = "jdbc:mysql://localhost:3306/test?user=root&password=89djm0210";
		
		try {
			Class.forName(driver);
			conn = (Connection) DriverManager.getConnection(connection);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	/**
	 * Closes the connection to the database
	 */
	private void disconnect() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * Executes update SQL statement
	 * These statement inlcude insert, delete, alter, etc
	 * @param updateSQL entire SQL statement, NOT PreparedStatment. Please Attention!
	 */
	public void update(String updateSQL){
		try {
			stmt = (Statement) conn.createStatement();
			stmt.executeUpdate(updateSQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * Executes SQL query
	 * @param querySQL entire SQL query, NOT PreparedStatement. Please Attention!
	 * @return result set of the execution of query
	 */
	public ResultSet query(String querySQL) {
		try {
			stmt = (Statement) conn.createStatement();
			return (ResultSet) stmt.executeQuery(querySQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Executes the update prepared statement
	 * @return the number of row(s) affected by the execution of the update statement
	 */
	public int updatePstmt() {
		try {
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	/**
	 * Executes SQL query, the prepared version
	 * @param querySQL prepared SQL query
	 * @return result set of the execution of query
	 */
	public ResultSet queryPstmt() {
		try {
			return (ResultSet) pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Creates the prepared statement for the connection
	 * @param pstmt the prepared statement which can be either an update or a query statement
	 */
	public void prepareStatement(String pstmt) {
		try {
			this.pstmt = (PreparedStatement) conn.prepareStatement(pstmt);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * Binds the parameter in the preparedStatement
	 * NOTE: you can pass different number of the parameters to the method,
	 * 		 but it should match the number of "?" in your prepared statement
	 * @param parameter the list of parameters of a variant length. 
	 */
	public void bindParameter(String... parameter) {
		try {
			for(int i = 0; i < parameter.length; i++) {
				pstmt.setString(i+1, parameter[i]);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
